﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using Framework;

namespace Enterprise
{
    public class ArticleService : BaseService
    {
        /// <summary>
        /// 获取文章列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string title, int articleCategoryId, string language, int pageValue, int rowsValue, string sort = null, string order = null)
        {
            if (sort.IsNullOrEmpty())
            {
                sort = "CoArticleId";
            }
            if (order.IsNullOrEmpty())
            {
                order = "DESC";
            }
            var parameters = new[]
                {
                    new SqlParameter("@CoTitle", SqlDbType.VarChar, 255,""),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoArticleCategoryId", SqlDbType.Int)
             };
            parameters[0].Value = title.SqlLike();
            parameters[1].Value = language.SqlLike();
            parameters[2].Value = articleCategoryId;
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where A.CoTitle like '%'+@CoTitle+'%' and A.CoLanguage like '%'+@CoLanguage+'%' ");
            if (articleCategoryId != 0)
            {
                sbSqlWhere.Append(" and A.CoArticleCategoryId=@CoArticleCategoryId ");
            }
            var sbSql = new StringBuilder();
            int total = DbHelperSql.GetSingle("select count(*) from TbArticle as A {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            sbSql.AppendFormat("select top {0} ", rowsValue);
            sbSql.Append(" A.CoArticleId,A.CoTitle,A.CoSmallImage,A.CoNormalImage,A.CoBigImage,A.CoArticleCategoryId,A.CoIsRecommend,A.CoIsPopular,A.CoIsTop,A.CoViews,A.CoAuthor,A.CoDate,A.CoIsReserved,A.CoReservedUrl,A.CoLanguage, B.CoName as CoCategoryName from TbArticle as A  ");
            sbSql.AppendFormat(" left join TbArticleCategory as B on  A.CoArticleCategoryId=B.CoArticleCategoryId {0} ", sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and A.CoArticleId not in (select top {0} A.CoArticleId from TbArticle as A {1} order by A.{2} {3} ) ", rowsValue * (pageValue - 1), sbSqlWhere, sort, order);
            sbSql.AppendFormat("order by A.{0} {1}", sort, order);
            var ds = DbHelperSql.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }

        public Table GetTableGroupByCategory(int count = 5, string sort = "CoDate", string order = "DESC")
        {
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select * from TbArticle a where a.CoArticleId in (select top {0} CoArticleId from TbArticle where CoArticleCategoryId = a.CoArticleCategoryId order by {1} {2}) order by {1} {2}", count, sort, order);
            var ds = DbHelperSql.Query(sbSql.ToString());
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = ds.Tables["ds"].Rows.Count,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }

        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSql.Query("select *  from TbArticle  where CoArticleId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
        /// <summary>
        /// 添加文章
        /// </summary>
        /// <param name="articleCategoryId"></param>
        /// <param name="title"></param>
        /// <param name="content"></param>
        /// <param name="summary"></param>
        /// <param name="author"></param>
        /// <param name="language"></param>
        /// <param name="reservedUrl"></param>
        /// <param name="date"></param>
        /// <param name="isRecommend"></param>
        /// <param name="isReserved"></param>
        /// <param name="isPopular"></param>
        /// <param name="isTop"></param>
        /// <param name="articleImageIds"></param>
        /// <param name="coverId"></param>
        /// <returns></returns>
        public int Add(int articleCategoryId, string title, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, int[] articleImageIds, int coverId)
        {
            Check.Argument.IsNotOutOfLength(title, 2, 50, "文章标题");
            Check.Argument.IsNotOutOfLength(summary, 252, "文章简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "文章语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoArticleCategoryId", SqlDbType.Int),
                    new SqlParameter("@CoCategoryPath", SqlDbType.Text),
                    new SqlParameter("@CoTitle", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoContent", SqlDbType.Text),
                    new SqlParameter("@CoSummary", SqlDbType.Text),
                    new SqlParameter("@CoReservedUrl", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAuthor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoIsReserved",SqlDbType.Bit),
                    new SqlParameter("@CoIsPopular",SqlDbType.Bit),
                    new SqlParameter("@CoIsTop",SqlDbType.Bit),
                    new SqlParameter("@CoIsRecommend",SqlDbType.Bit),  
                    new SqlParameter("@CoViews", SqlDbType.Int),
                    new SqlParameter("@CoCoverImageId", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255)
                };
            var sbSql = new StringBuilder();
            sbSql.Append("insert into TbArticle(");
            sbSql.Append("CoArticleCategoryId,CoCategoryPath,CoTitle,CoContent,CoSummary,CoReservedUrl,CoAuthor,CoLanguage,CoDate,CoIsReserved,CoIsPopular,CoIsTop,CoIsRecommend,CoViews,CoCoverImageId,CoNormalImage,CoSmallImage,CoBigImage");
            sbSql.Append(")");
            sbSql.Append(" values (");
            sbSql.Append("@CoArticleCategoryId,@CoCategoryPath,@CoTitle,@CoContent,@CoSummary,@CoReservedUrl,@CoAuthor,@CoLanguage,@CoDate,@CoIsReserved,@CoIsPopular,@CoIsTop,@CoIsRecommend,@CoViews,@CoCoverImageId,@CoNormalImage,@CoSmallImage,@CoBigImage");
            sbSql.Append(")");
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbArticleCategory where CoArticleCategoryId={0}".FormatWith(articleCategoryId)))
            {
                if (sdr.Read())
                {
                    parameters[0].Value = articleCategoryId;
                    parameters[1].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parameters[0].Value = DBNull.Value;
                    parameters[1].Value = "";
                }
            }
            parameters[2].Value = title;
            parameters[3].Value = content;
            parameters[4].Value = summary;
            parameters[5].Value = reservedUrl;
            parameters[6].Value = author;
            parameters[7].Value = language;
            parameters[8].Value = date;
            parameters[9].Value = isReserved;
            parameters[10].Value = isPopular;
            parameters[11].Value = isTop;
            parameters[12].Value = isRecommend;
            parameters[13].Value = 0;
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbArticleImage where CoArticleImageId={0}".FormatWith(coverId)))
            {
                if (sdr.Read())
                {
                    parameters[14].Value = coverId;
                    parameters[15].Value = sdr["CoNormalImage"].SafeToString();
                    parameters[16].Value = sdr["CoSmallImage"].SafeToString();
                    parameters[17].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parameters[14].Value = DBNull.Value;
                    parameters[15].Value = DBNull.Value;
                    parameters[16].Value = DBNull.Value;
                    parameters[17].Value = DBNull.Value;
                }
            }
            var obj = DbHelperSql.Add(sbSql.ToString(), parameters).SafeToInt();
            if (obj > 0)
            {
                DbHelperSql.ExecuteSqlTran(new ArrayList
                    {
                        "update TbArticleImage set CoArticleId={0} where CoArticleImageId in ({1}) and  exists(select * from tbarticle where coarticleid={0})".FormatWith(obj,string.Join(",",articleImageIds.Select(o =>o.ToString(CultureInfo.InvariantCulture)).ToArray())),
                        "update TbArticleImage set CoIsCover=1 where CoArticleImageId={0}".FormatWith(coverId)

                    });
            }
            return obj;
        }
        public int Edit(int id, int articleCategoryId, string title, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, int coverId)
        {
            Check.Argument.IsNotOutOfLength(title, 2, 50, "文章标题");
            Check.Argument.IsNotOutOfLength(summary, 252, "文章简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "文章语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var sbSql = new StringBuilder();
            sbSql.Append("update TbArticle set ");
            sbSql.Append("CoArticleCategoryId=@CoArticleCategoryId");
            sbSql.Append(",");
            sbSql.Append("CoCategoryPath=@CoCategoryPath");
            sbSql.Append(",");
            sbSql.Append("CoTitle=@CoTitle");
            sbSql.Append(",");
            sbSql.Append("CoContent=@CoContent");
            sbSql.Append(",");
            sbSql.Append("CoSummary=@CoSummary");
            sbSql.Append(",");
            sbSql.Append("CoReservedUrl=@CoReservedUrl");
            sbSql.Append(",");
            sbSql.Append("CoAuthor=@CoAuthor");
            sbSql.Append(",");
            sbSql.Append("CoLanguage=@CoLanguage");
            sbSql.Append(",");
            sbSql.Append("CoDate=@CoDate");
            sbSql.Append(",");
            sbSql.Append("CoIsReserved=@CoIsReserved");
            sbSql.Append(",");
            sbSql.Append("CoIsPopular=@CoIsPopular");
            sbSql.Append(",");
            sbSql.Append("CoIsTop=@CoIsTop");
            sbSql.Append(",");
            sbSql.Append("CoIsRecommend=@CoIsRecommend");
            sbSql.Append(",");
            sbSql.Append("CoCoverImageId=@CoCoverImageId");
            sbSql.Append(",");
            sbSql.Append("CoNormalImage=@CoNormalImage");
            sbSql.Append(",");
            sbSql.Append("CoSmallImage=@CoSmallImage");
            sbSql.Append(",");
            sbSql.Append("CoBigImage=@CoBigImage");
            sbSql.Append(" where CoArticleId=@id");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoArticleCategoryId", SqlDbType.Int),
                    new SqlParameter("@CoCategoryPath", SqlDbType.Text),
                    new SqlParameter("@CoTitle", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoContent", SqlDbType.Text),
                    new SqlParameter("@CoSummary", SqlDbType.Text),
                    new SqlParameter("@CoReservedUrl", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAuthor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoIsReserved",SqlDbType.Bit),
                    new SqlParameter("@CoIsPopular",SqlDbType.Bit),
                    new SqlParameter("@CoIsTop",SqlDbType.Bit),
                    new SqlParameter("@CoIsRecommend",SqlDbType.Bit),  
                    new SqlParameter("@CoCoverImageId", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@id", SqlDbType.Int)
                };
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbArticleCategory where CoArticleCategoryId={0}".FormatWith(articleCategoryId)))
            {
                if (sdr.Read())
                {
                    parameters[0].Value = articleCategoryId;
                    parameters[1].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parameters[0].Value = DBNull.Value;
                    parameters[1].Value = "";
                }
            }
            parameters[2].Value = title;
            parameters[3].Value = content;
            parameters[4].Value = summary;
            parameters[5].Value = reservedUrl;
            parameters[6].Value = author;
            parameters[7].Value = language;
            parameters[8].Value = date;
            parameters[9].Value = isReserved;
            parameters[10].Value = isPopular;
            parameters[11].Value = isTop;
            parameters[12].Value = isRecommend;
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbArticleImage where CoArticleImageId={0}".FormatWith(coverId)))
            {
                if (sdr.Read())
                {
                    parameters[13].Value = coverId;
                    parameters[14].Value = sdr["CoNormalImage"].SafeToString();
                    parameters[15].Value = sdr["CoSmallImage"].SafeToString();
                    parameters[16].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parameters[13].Value = DBNull.Value;
                    parameters[14].Value = DBNull.Value;
                    parameters[15].Value = DBNull.Value;
                    parameters[16].Value = DBNull.Value;
                }
            }
            parameters[17].Value = id;
            var obj = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (obj > 0)
            {
                DbHelperSql.ExecuteSqlTran(new ArrayList
                    {
                        "update TbArticleImage set CoIsCover=0 where CoArticleId={0} and CoIsCover=1".FormatWith(id),
                        "update TbArticleImage set CoIsCover=1 where CoArticleImageId={0}".FormatWith(coverId)
                     });
            }
            return obj;

        }
        public void Delete(int id)
        {
            DbHelperSql.ExecuteSqlTran(new ArrayList
                {
                    "delete from TbArticleImage  where CoArticleId={0}".FormatWith(id),
                    "delete from TbArticle  where CoArticleId={0}".FormatWith(id)
                });
        }
    }
}
